1 Imports System.Data.SqlClient
2 Public Class frmInstallment_Bus
3 Private Sub auto()
4 Try
5 Dim Num As Integer = 0
6 con = New SqlConnection(cs)
7 con.Open()
8 Dim sql As String = ("SELECT MAX(IB_ID) FROM Installment_Bus")
9 cmd = New SqlCommand(sql)
10 cmd.Connection = con
11 If (IsDBNull(cmd.ExecuteScalar)) Then
12 Num = 1
13 txtID.Text = Num.ToString
14 Else
15 Num = cmd.ExecuteScalar + 1
16 txtID.Text = Num.ToString
17 End If
18 cmd.Dispose()
19 con.Close()
20 con.Dispose()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25 Sub fillCombo()
26 Try
27 con = New SqlConnection(cs)
28 con.Open()
29 adp = New SqlDataAdapter()
30 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(LocationName) FROM Location", con)
31 ds = New DataSet("ds")
32 adp.Fill(ds)
33 dtable = ds.Tables(0)
34 cmbLocation.Items.Clear()
35 For Each drow As DataRow In dtable.Rows
36 cmbLocation.Items.Add(drow(0).ToString())
37 Next
38 Catch ex As Exception
39 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
40 End Try
41 End Sub
42 Sub Reset()
43 txtInstallment.Text = ""
44 cmbLocation.SelectedIndex = -1
45 txtCharges.Text = ""
46 txtInstallment.Focus()
47 btnSave.Enabled = True
48 btnUpdate.Enabled = False
49 btnDelete.Enabled = False
50 txtSearchByLocation.Text = ""
51 Getdata()
52 auto()
53 End Sub
54 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
55 Me.Close()
56 End Sub
57
58 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
59
60 If Len(Trim(txtInstallment.Text)) = 0 Then
61 MessageBox.Show("Please enter installment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
62 txtInstallment.Focus()
63 Exit Sub
64 End If
65 If Len(Trim(cmbLocation.Text)) = 0 Then
66 MessageBox.Show("Please select location", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
67 cmbLocation.Focus()
68 Exit Sub
69 End If
70 If Len(Trim(txtCharges.Text)) = 0 Then
71 MessageBox.Show("Please enter charges", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
72 txtCharges.Focus()
73 Exit Sub
74 End If
75 Try
76 con = New SqlConnection(cs)
77 con.Open()
78 Dim ct As String = "select Installment,Location from Installment_Bus where Installment=@d1 and Location=@d2"
79 cmd = New SqlCommand(ct)
80 cmd.Connection = con
81 cmd.Parameters.AddWithValue("@d1", txtInstallment.Text)
82 cmd.Parameters.AddWithValue("@d2", cmbLocation.Text)
83 rdr = cmd.ExecuteReader()
84 If rdr.Read() Then
85 MessageBox.Show("Record Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
86 If (rdr IsNot Nothing) Then
87 rdr.Close()
88 End If
89 Return
90 End If
91 con = New SqlConnection(cs)
92 con.Open()
93 Dim cb As String = "insert into Installment_Bus(IB_ID,Installment,Charges,Location) VALUES (" & txtID.Text & ",@d1,@d2,@d3)"
94 cmd = New SqlCommand(cb)
95 cmd.Connection = con
96 cmd.Parameters.AddWithValue("@d1", txtInstallment.Text)
97 cmd.Parameters.AddWithValue("@d2", txtCharges.Text)
98 cmd.Parameters.AddWithValue("@d3", cmbLocation.Text)
99 cmd.ExecuteReader()
100 con.Close()
101 LogFunc(lblUser.Text, "added the new Installment '" & txtInstallment.Text & "' for location '" & cmbLocation.Text & "'")
102 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
103 btnSave.Enabled = False
104 Getdata()
105 Autocomplete()
106 Catch ex As Exception
107 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
108 End Try
109 End Sub
110
111 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
112 Try
113 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
114 DeleteRecord()
115 End If
116 Catch ex As Exception
117 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
118 End Try
119 End Sub
120 Private Sub DeleteRecord()
121
122 Try
123 con = New SqlConnection(cs)
124 con.Open()
125 Dim cl As String = "select InstallmentID from Installment_Bus,HostelFeePayment where Installment_Bus.IB_ID=HostelFeePayment.InstallmentID and InstallmentID=@d1"
126 cmd = New SqlCommand(cl)
127 cmd.Connection = con
128 cmd.Parameters.AddWithValue("@d1", txtID.Text)
129 rdr = cmd.ExecuteReader()
130 If rdr.Read Then
131 MessageBox.Show("Unable to delete..Already in use in Bus Fee Payment [Student]", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
132 If Not rdr Is Nothing Then
133 rdr.Close()
134 End If
135 Exit Sub
136 End If
137 Dim RowsAffected As Integer = 0
138 con = New SqlConnection(cs)
139 con.Open()
140 Dim cq As String = "delete from Installment_Bus where IB_ID=@d1"
141 cmd = New SqlCommand(cq)
142 cmd.Connection = con
143 cmd.Parameters.AddWithValue("@d1", txtID.Text)
144 RowsAffected = cmd.ExecuteNonQuery()
145 If RowsAffected > 0 Then
146 LogFunc(lblUser.Text, "deleted the Installment '" & txtInstallment.Text & "' of location '" & cmbLocation.Text & "'")
147 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
148 Getdata()
149 Reset()
150 Autocomplete()
151 Else
152 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
153 Reset()
154 End If
155 If con.State = ConnectionState.Open Then
156 con.Close()
157
158 End If
159 Catch ex As Exception
160 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
161 End Try
162 End Sub
163
164 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
165
166 If Len(Trim(txtInstallment.Text)) = 0 Then
167 MessageBox.Show("Please enter installment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
168 txtInstallment.Focus()
169 Exit Sub
170 End If
171 If Len(Trim(cmbLocation.Text)) = 0 Then
172 MessageBox.Show("Please select location", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
173 cmbLocation.Focus()
174 Exit Sub
175 End If
176 If Len(Trim(txtCharges.Text)) = 0 Then
177 MessageBox.Show("Please enter charges", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
178 txtCharges.Focus()
179 Exit Sub
180 End If
181 Try
182 con = New SqlConnection(cs)
183 con.Open() '
184 Dim cb As String = "Update Installment_Bus set Installment=@d1,Charges=@d2,Location=@d3 where IB_ID=" & txtID.Text & ""
185 cmd = New SqlCommand(cb)
186 cmd.Connection = con
187 cmd.Parameters.AddWithValue("@d1", txtInstallment.Text)
188 cmd.Parameters.AddWithValue("@d2", txtCharges.Text)
189 cmd.Parameters.AddWithValue("@d3", cmbLocation.Text)
190 cmd.ExecuteReader()
191 con.Close()
192 LogFunc(lblUser.Text, "updated the Installment '" & txtInstallment.Text & "' of location '" & cmbLocation.Text & "'")
193 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
194 btnUpdate.Enabled = False
195 Getdata()
196 Autocomplete()
197 Catch ex As Exception
198 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
199 End Try
200 End Sub
201 Public Sub Getdata()
202 Try
203 con = New SqlConnection(cs)
204 con.Open()
205 cmd = New SqlCommand("SELECT RTRIM(IB_ID), RTRIM(Installment),RTRIM(Location),RTRIM(Charges) from Installment_Bus order by Location,installment", con)
206 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
207 dgw.Rows.Clear()
208 While (rdr.Read() = True)
209 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3))
210 End While
211 con.Close()
212 Catch ex As Exception
213 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
214 End Try
215 End Sub
216 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
217 Reset()
218 End Sub
219
220
221 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
222 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
223 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
224 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
225 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
226 End If
227 Dim b As Brush = SystemBrushes.ControlText
228 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
229
230 End Sub
231
232 Private Sub frmCategory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
233 Getdata()
234 fillCombo()
235 Autocomplete()
236 End Sub
237
238 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
239 Try
240 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
241 txtInstallment.Text = dr.Cells(1).Value.ToString()
242 txtID.Text = dr.Cells(0).Value.ToString()
243 cmbLocation.Text = dr.Cells(2).Value.ToString()
244 txtCharges.Text = dr.Cells(3).Value.ToString()
245 btnUpdate.Enabled = True
246 btnDelete.Enabled = True
247 btnSave.Enabled = False
248 Catch ex As Exception
249 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
250 End Try
251 End Sub
252 Sub Autocomplete()
253 Try
254 con = New SqlConnection(cs)
255 con.Open()
256 cmd = New SqlCommand("SELECT Distinct Installment from Installment_Bus", con)
257 ds = New DataSet()
258 adp = New SqlDataAdapter(cmd)
259 adp.Fill(ds, "Installment")
260 Dim col As AutoCompleteStringCollection = New AutoCompleteStringCollection()
261 Dim i As Integer = 0
262 For i = 0 To ds.Tables(0).Rows.Count - 1
263 col.Add(ds.Tables(0).Rows(i)("Installment").ToString())
264 Next
265 txtInstallment.AutoCompleteSource = AutoCompleteSource.CustomSource
266 txtInstallment.AutoCompleteCustomSource = col
267 txtInstallment.AutoCompleteMode = AutoCompleteMode.Suggest
268 con.Close()
269 Catch ex As Exception
270 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
271 End Try
272 End Sub
273
274 Private Sub txtFee_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtCharges.KeyPress
275 Dim keyChar = e.KeyChar
276
277 If Char.IsControl(keyChar) Then
278 'Allow all control characters.
279 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
280 Dim text = Me.txtCharges.Text
281 Dim selectionStart = Me.txtCharges.SelectionStart
282 Dim selectionLength = Me.txtCharges.SelectionLength
283
284 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
285
286 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
287 'Reject an integer that is longer than 16 digits.
288 e.Handled = True
289 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
290 'Reject a real number with two many decimal places.
291 e.Handled = False
292 End If
293 Else
294 'Reject all other characters.
295 e.Handled = True
296 End If
297 End Sub
298
299 Private Sub txtSearchByLocation_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByLocation.TextChanged
300 Try
301 con = New SqlConnection(cs)
302 con.Open()
303 cmd = New SqlCommand("SELECT RTRIM(IB_ID), RTRIM(Installment),RTRIM(Location),RTRIM(Charges) from Installment_Bus where Location like '" & txtSearchByLocation.Text & "%' order by Location,installment", con)
304 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
305 dgw.Rows.Clear()
306 While (rdr.Read() = True)
307 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3))
308 End While
309 con.Close()
310 Catch ex As Exception
311 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
312 End Try
313 End Sub
314
315 Private Sub txtInstallment_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtInstallment.TextChanged
316 txtInstallment.Text = txtInstallment.Text.Trim()
317 End Sub
318 End Class
